﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataAccess.IService;
using System.Data;
using Utility;
using System.Collections;
using System.Data.SqlClient;
using Entity;
namespace DataAccess.Service
{
    public class OrderService:IOrderService
    {
        //14个
        public String selectUtilityOrderId()
        {
            String result = "";
            string commandText = string.Format("select dbo.f_NextUtilityOrderId()"); // 查询sql语句
            result = SQLHelper.ExecuteScalar(commandText).ToString();
            return result;
        }
        public String selectPortOrderId()
        {
            String result = "";
           // string commandText = string.Format("select max(orderId) from CampusNetworkHistoryOrder"); // 查询sql语句
            string commandText = string.Format("select dbo.f_NextNetworkOrderId()");
           
            result = SQLHelper.ExecuteScalar(commandText).ToString();
            
            return result;
        }

        public String selectStuCardOrderId()
        {
            String result = "";
            string commandText = string.Format("select dbo.f_NextStuCardOrderId()"); // 查询sql语句
            result = SQLHelper.ExecuteScalar(commandText).ToString();
            return result;
        }
        public String selectPaymentOrderId()
        {
            String result = "";
            string commandText = string.Format("select dbo.f_NextOrderId()"); // 查询sql语句
            result = SQLHelper.ExecuteScalar(commandText).ToString();
            return result;
        }
        public bool insertUtilityOrder(BankPay bankPay,string stuId)
        {
            bool result = false;
            string commandText = "Insert into UtilitiesHistoryOrder(orderId,orderStatus,totalAmount,orderDate,orderBank,orderAccount,utilityId,stuId) values(@orderId,@orderStatus,@totalAmount,@orderDate,@orderBank,@orderAccount,@utilityId,@stuId)";
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("orderId", bankPay.OrderId),
                new SqlParameter("orderStatus",bankPay.Msg),
                new SqlParameter("totalAmount", bankPay.Amount),
                new SqlParameter("orderDate", bankPay.OrderDate),
                new SqlParameter("orderBank", bankPay.BankId),
                new SqlParameter("orderAccount", bankPay.PayAccount),
                new SqlParameter("stuId",stuId),
                new SqlParameter("utilityId", bankPay.GoodsID)
            };
            if (SQLHelper.ExecuteNonQuery(commandText, paras) > 0)
            {
                result = true;
            }
            return result;
;
        }

        public bool insertPortOrder(BankPay bankPay, string stuId)
        {

            bool result = false;
            string commandText = "Insert into CampusNetworkHistoryOrder(orderId,orderStatus,totalAmount,orderDate,orderBank,orderAccount,stuId,portId) values(@orderId,@orderStatus,@totalAmount,@orderDate,@orderBank,@orderAccount,@stuId,@portId)";
            SqlParameter[] paras = new SqlParameter[]
            {
                 new SqlParameter("orderId", bankPay.OrderId),
                new SqlParameter("orderStatus", bankPay.Msg),
                new SqlParameter("totalAmount", bankPay.Amount),
                new SqlParameter("orderDate", bankPay.OrderDate),
                new SqlParameter("orderBank", bankPay.BankId),
                new SqlParameter("orderAccount", bankPay.PayAccount),
                new SqlParameter("stuId", stuId),
                new SqlParameter("portId", bankPay.GoodsID)
            };
            if (SQLHelper.ExecuteNonQuery(commandText, paras) > 0)
            {
                result = true;
            }
            return result;
           
        }
        public bool insertStuCardOrder(BankPay bankPay, string stuId)
        {
            bool result = false;
            string commandText = "Insert into StudentCartHistoryOrder(orderId,orderStatus,totalAmount,orderDate,orderBank,orderAccount,stuCardId) values(@orderId,@orderStatus,@totalAmount,@orderDate,@orderBank,@orderAccount,@stuCardId)";
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("orderId", bankPay.OrderId),
                new SqlParameter("orderStatus", bankPay.Msg),
                new SqlParameter("totalAmount", bankPay.Amount),
                new SqlParameter("orderDate", bankPay.OrderDate),
                new SqlParameter("orderBank", bankPay.BankId),
                new SqlParameter("orderAccount", bankPay.PayAccount),
                new SqlParameter("stuCardId", bankPay.GoodsID)
            };
            if (SQLHelper.ExecuteNonQuery(commandText, paras) > 0)
            {
                result = true;
            }
            return result;
        }


        /// <summary>
        /// 同时插入多个缴费订单
        /// 误：应该只插入一个缴费订单才对
        /// </summary>
        /// <param name="bankPay"></param>
        /// <param name="orders"></param>
        /// <returns></returns>
        public bool insertProjectOrder(BankPay bankPay, List<ProjectOrder> orders)
        {
            bool result = false;
            /*string commandText=string.Format( "Insert into PaymentOrder(orderId,stuProjectId,orderStatus,totalAmount,orderDate,orderBank,orderAccount) values(@orderId,@stuProjectId,@orderStatus,@totalAmount,@orderDate,@orderBank,@orderAccount)");
            //for (int i = 0; i < orders.Count; i++)
            //{
                SqlParameter[] paras = new SqlParameter[]
                {  
                    new SqlParameter("orderStatus", bankPay.Msg),
                    new SqlParameter("totalAmount", bankPay.Amount),
                    new SqlParameter("orderDate", bankPay.OrderDate),
                    new SqlParameter("orderBank", bankPay.BankId),
                    new SqlParameter("orderAccount", bankPay.PayAccount),
                    new SqlParameter("stuProjectId", orders[i].ProjectId),
                    new SqlParameter("orderId", orders[i].OrderId)
                };
                if (SQLHelper.ExecuteNonQuery(commandText, paras) > 0)
                {
                    result = true;
                }
             // }*/
            return result;
        }



        



        /// <summary>
        /// 同时插入多个缴费订单详细
        /// </summary>
        /// <param name="details"></param>
        /// <param name="orderId"></param>
        /// <returns></returns>
        public bool insertProjectDetailOrder(List<OrderDetail> details, String orderId)
        {
            bool result = false;
            string commandText= string.Format("Insert into PaymentOrderDetail(orderId,stuProjectId,detailId,amount) values(@orderId,@stuProjectId,@detailId,@amount)");
            for (int i = 0; i < details.Count; i++)
            {
                SqlParameter[] paras = new SqlParameter[]
               {      
                 new SqlParameter("orderId", orderId),
                 new SqlParameter("stuProjectId", details[i].ProjectId),
                 new SqlParameter("detailId", details[i].DetailId),
                 new SqlParameter("amount", details[i].Amount)
               };
               if (SQLHelper.ExecuteNonQuery(commandText, paras) > 0)
               {
                    result = true;
               }   
            }
            return result;
        }
        public DataSet selectStuCardOrderInfor(String stuId)
        {
           
            string commandText= "select * from StudentCartHistoryOrder,StudentCard where StudentCartHistoryOrder.stuCardId=StudentCard.stuCardId and stuId='" + stuId + "';";
            return SQLHelper.ExecuteDataSet(commandText); 

        }
        public DataSet selectUtilityOrderInfor(String stuId)
        {
            
            string commandText = "select * from UtilityPayment,StudentAccommodation,UtilitiesHistoryOrder where UtilitiesHistoryOrder.utilityId=UtilityPayment.utilityId and StudentAccommodation.dorId=UtilityPayment.dorId and UtilitiesHistoryOrder.stuId='" + stuId + "'";
            return SQLHelper.ExecuteDataSet(commandText);;
        }
        public DataSet selectNetworkPortOrderInfor(String stuId)
        {
           
             string commandText= "select * from StudentAccommodation,CampusNetworkHistoryOrder where StudentAccommodation.portId=CampusNetworkHistoryOrder.portId and StudentAccommodation.stuId='" + stuId + "'";
             return SQLHelper.ExecuteDataSet(commandText);
        }
        public DataSet selectPaymentProjectInfor(String stuId)
        {
           
              string commandText = "select * from PaymentOrder,StudentPaymentProject,PaymentProject where PaymentOrder.stuProjectId=StudentPaymentProject.stuProjectId and PaymentProject.projectId=StudentPaymentProject.projectId and stuId='" + stuId + "'";
              return SQLHelper.ExecuteDataSet(commandText);
        }
        public DataSet selectProjectDetailInfor(String orderId, int stuProjectId)
        {
    
              string commandText = "select * from PaymentOrderDetail where orderId='" + orderId + "'and stuProjectId='" + stuProjectId + "'";
              return SQLHelper.ExecuteDataSet(commandText);
        }



        /// <summary>
        /// 获取学费订单信息，管理一般项的三张表
        /// </summary>
        /// <returns></returns>
        public DataSet getTuitionOrderSet()
        {
            string commandText = "select * " +
                    "from PaymentOrder,StudentPaymentProject,PaymentProject " +
                    "where PaymentOrder.stuProjectId = StudentPaymentProject.stuProjectId " +
                        "and StudentPaymentProject.projectId = PaymentProject.projectId";
            return SQLHelper.ExecuteDataSet(commandText);
        }






        public DataSet getTuitionSet()
        {
            string commandText = "select * " +
                    "from PaymentOrder,StudentPaymentProject,PaymentProject " +
                    "where PaymentOrder.stuProjectId = StudentPaymentProject.stuProjectId " +
                        "and StudentPaymentProject.projectId = PaymentProject.projectId";
            return SQLHelper.ExecuteDataSet(commandText);
        }


        public DataSet getTuitionDetailSet(String orderId)
        {
            string commandText = "select * " +
                             "from PaymentOrder,PaymentOrderDetail,StudentPaymentProject,StudentPaymentProjectDetail,PaymentProject,PaymentProjectDetail " +
                             "where PaymentOrder.stuProjectId = StudentPaymentProject.stuProjectId " +
                                 "and StudentPaymentProject.projectId = PaymentProject.projectId " +
                                 "and PaymentOrderDetail.orderId = PaymentOrder.orderId " +
                                 "and PaymentOrderDetail.stuProjectId = StudentPaymentProject.stuProjectId " +
                                 "and PaymentOrderDetail.detailId = StudentPaymentProjectDetail.detailId " +
                                 "and StudentPaymentProjectDetail.stuProjectId = StudentPaymentProject.stuProjectId " +
                                 "and PaymentProjectDetail.projectId = PaymentProject.projectId " +
                                 "and StudentPaymentProjectDetail.detailId = PaymentProjectDetail.detailId " +
                                 "and PaymentOrder.orderId = @orderId ";
            SqlParameter[] paras = new SqlParameter[]
            {  
                new SqlParameter("@orderId",orderId)
            };
            return SQLHelper.ExecuteDataSet(commandText,CommandType.Text,paras);
        }








        public bool addProjectOrder(BankPay bankPay, ProjectOrder order)
        {
            bool result = false;
            string commandText = string.Format("Insert into PaymentOrder(orderId,stuProjectId,orderStatus,totalAmount,orderDate,orderBank,orderAccount) values(@orderId,@stuProjectId,@orderStatus,@totalAmount,@orderDate,@orderBank,@orderAccount)");

            SqlParameter[] paras = new SqlParameter[]
            {  
                new SqlParameter("orderStatus", bankPay.Msg),
                new SqlParameter("totalAmount", bankPay.Amount),
                new SqlParameter("orderDate", bankPay.OrderDate),
                new SqlParameter("orderBank", bankPay.BankId),
                new SqlParameter("orderAccount", bankPay.PayAccount),
                new SqlParameter("stuProjectId", order.ProjectId),
                new SqlParameter("orderId", bankPay.OrderId)
            };
            if (SQLHelper.ExecuteNonQuery(commandText, paras) > 0)
            {
                result = true;
            }
            return result;
        }



        public bool addProjectDetailOrder(OrderDetail orderDetail, String orderId)
        {
            bool result = false;
            string commandText = string.Format("Insert into PaymentOrderDetail(orderId,stuProjectId,detailId,amount) values(@orderId,@stuProjectId,@detailId,@amount)");

            SqlParameter[] paras = new SqlParameter[]
            {      
                new SqlParameter("orderId", orderId),
                new SqlParameter("stuProjectId", orderDetail.ProjectId),
                new SqlParameter("detailId", orderDetail.DetailId),
                new SqlParameter("amount", orderDetail.Amount)
            };
            if (SQLHelper.ExecuteNonQuery(commandText, paras) > 0)
            {
                result = true;
            }
            return result;
        }











        public DataSet getTuitionOrderSetByStudentId(string studentId)
        {
            string commandText = "select * " + 
                            "from PaymentOrder,StudentPaymentProject,PaymentProject " + 
                            "where PaymentOrder.stuProjectId = StudentPaymentProject.stuProjectId " +
                                "and StudentPaymentProject.projectId = PaymentProject.projectId " +
                                "and StudentPaymentProject.stuId = @stuId";
            SqlParameter[] paras = new SqlParameter[]
            {      
                new SqlParameter("@stuId", studentId)
            };
            return SQLHelper.ExecuteDataSet(commandText, CommandType.Text, paras);
        }
        public DataSet getStuCardOrderSetByStudentId(string studentId)
        {
            string commandText = "select * " +
                         "from StudentCartHistoryOrder,StudentCard " +
                         "where StudentCartHistoryOrder.stuCardId = StudentCard.stuCardId " +
                          "and StudentCard.stuId = @stuId";
            SqlParameter[] paras = new SqlParameter[]
            {      
                new SqlParameter("@stuId", studentId)
            };
            return SQLHelper.ExecuteDataSet(commandText,CommandType.Text,paras);
        }
        public DataSet getNetworkOrderSetByStudentId(string studentId)
        {
            string commandText = "select * " +
                         "from StudentCartHistoryOrder,StudentCard " +
                         "where StudentCartHistoryOrder.stuCardId = StudentCard.stuCardId " +
                          "and StudentCard.stuId = @stuId";
            SqlParameter[] paras = new SqlParameter[]
            {      
                new SqlParameter("@stuId", studentId)
            };
            return SQLHelper.ExecuteDataSet(commandText, CommandType.Text, paras);
        }
        public DataSet getUtilityOrderSetByStudentId(string studentId)
        {
            string commandText = "select * " +
                         "from StudentCartHistoryOrder,StudentCard " +
                         "where StudentCartHistoryOrder.stuCardId = StudentCard.stuCardId " +
                          "and StudentCard.stuId = @stuId";
            SqlParameter[] paras = new SqlParameter[]
            {      
                new SqlParameter("@stuId", studentId)
            };
            return SQLHelper.ExecuteDataSet(commandText, CommandType.Text, paras);
        }


        public DataSet getTuitionOrderDetailByOrderId(string orderId)
        {
            string commandText = "select * " +
                            "from PaymentOrderDetail,StudentPaymentProjectDetail,PaymentProjectDetail " +
                            "where PaymentOrderDetail.detailId = StudentPaymentProjectDetail.detailId " +
	                            "and PaymentOrderDetail.stuProjectId = StudentPaymentProjectDetail.stuProjectId " +
                                "and StudentPaymentProjectDetail.detailId = PaymentProjectDetail.detailId " +
	                            "and PaymentOrderDetail.orderId = @orderId";
            SqlParameter[] paras = new SqlParameter[]
            {      
                new SqlParameter("@orderId", orderId)
            };
            return SQLHelper.ExecuteDataSet(commandText, CommandType.Text, paras);
        }

    }
}
